package dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

public class Habitat {
	
	//Helper string
    public static final String staticFields =
            "Habitat.summer_temperature_average, " +
            "Habitat.winter_temperature_average, " +
            "Habitat.habitat_name, " +
            "Bird.weight";
    
    //Private fields
    private String habitatName;
    private int summerTemperature;
    private int winterTemperature;
    private String state;
    private String precipitation;
    
    //Class constructor
    public Habitat(String precipitation, String habitatName, int summerTemperature, int winterTemperature, String state){
    	this.habitatName = habitatName;
    	this.summerTemperature = summerTemperature;
    	this.winterTemperature = winterTemperature;
    	this.state = state;
    	this.precipitation = precipitation;
    }
    
    //CUD Queries
    
    public void insert() throws SQLException{
    	String sql;
    	
    	sql = "INSERT INTO Habitat VALUES ('" + precipitation + "', " + summerTemperature + ", " + winterTemperature + ", '" + habitatName + "')";
    	
    	ConnectionManager.getStatement().executeUpdate(sql); //inserts the habitat 	
    	
    	sql = "INSERT INTO State VALUES ('" + state + "')"; //inserts the state, if not exists
    	
    	try{
    		ConnectionManager.getStatement().executeUpdate(sql);
    	}
    	catch(SQLException ex){
    		System.out.println("State already Exists. Just ignoring this insert");
    	}
    	
    	sql = "INSERT INTO State_has_habitat VALUES ('" + state + "', '" +  habitatName +"')"; //associates the state with its habitat
    	
    	ConnectionManager.getStatement().executeUpdate(sql);
    	
    }
    
    public void remove() throws SQLException{
    	String sql;
    	
    	sql = "DELETE FROM State_has_habitat where Habitat_habitat_name = '" + habitatName + "'";
    	
    	ConnectionManager.getStatement().executeUpdate(sql);
    	
    	sql = "DELETE FROM Habitat where habitat_name = '" + habitatName +"'";
    	
    	ConnectionManager.getStatement().executeUpdate(sql);
    	
    }
    
    public void update() throws SQLException{
    	String sql;
    	
    	sql = "UPDATE Habitat SET summer_temperature_average = " + summerTemperature + ", winter_temperature_average = " + winterTemperature + " WHERE habitat_name = '" + habitatName + "'";
    	
    	ConnectionManager.getStatement().executeUpdate(sql);
    }
 
    //Lists all Habitats with all fields already filled
    public static Vector<Habitat> listAll() throws SQLException{   
    	
    	return runSQLQuery("SELECT * FROM Habitat, State_has_Habitat, State where State_has_habitat.State_state_name = State.state_name and Habitat.habitat_name = State_has_habitat.Habitat_habitat_name");
    }
    
    
    //receives a bird and associates it with this habitat
    public void associateBird(Bird bird) throws SQLException{
    	String sql;
    	
    	sql = "INSERT INTO Bird_has_habitat VALUES ('" + habitatName + "', '" + bird.getScientificName() + "')";
    	
    	ConnectionManager.getStatement().executeUpdate(sql);
    }
    
    
    //Desasociates bird with this habitat. Returns TRUE if desassociated, FALSE if not
    public Boolean desassociateBird(Bird bird){
    	
    	String sql = "DELETE FROM Bird_has_habitat WHERE Habitat_habitat_name = '" + habitatName + "' and Bird_scientific_name = '" + bird.getScientificName() + "'";
    	
    	try{
    		
    		ConnectionManager.getStatement().executeUpdate(sql);
    		return true;
    	}
    	
    	catch(SQLException ex){
    		return false;
    		
    		
    	}
    }
    
    //Gets an arbitrary sql and executes it and returns a Habitat vector
    
    public static Vector<Habitat> runSQLQuery(String SQLQuery)
    throws SQLException
    {
            ResultSet result;
            Statement statement;
            Vector<Habitat> habitats = new Vector<Habitat>();

            statement = ConnectionManager.getStatement();
            result = statement.executeQuery(SQLQuery);

            while (result.next()) {
                    Habitat h;

                    h = new Habitat(result.getString("precipitation"), result.getString("habitat_name"),
                                 result.getInt("summer_temperature_average"),
                                 result.getInt("winter_temperature_average"),
                                 result.getString("state_name"));

                    habitats.add(h);
            }

            return habitats;
    }
    
    
    
    
    //Getters
    public String getPrecipitation(){
    	return this.precipitation;
    }
    
    public String getName(){
    	return this.habitatName;
    }
    
    public int getSummerTemperature(){
    	return this.summerTemperature;
    }
    
    public int getWinterTemperature(){
    	return this.winterTemperature;
    }
    
    public String getState(){
    	return this.state;
    }
    
	//Setters
    public void setPrecipitation(String precipitation){
    	this.precipitation = precipitation;
    }
    
	public void setName(String habitatName){
		this.habitatName = habitatName;
	}
	
	public void setSummerTemperature(int summerTemperature){
		this.summerTemperature = summerTemperature;
	}
	
	public void setWinterTemperature(int winterTemperature){
		this.winterTemperature = winterTemperature;
	}
	
	public void setState(String state){
		this.state = state;
	}
    
	
    
}
